Variables are used to store values that can then be passed into tables for incremental loading, and injected into scripts, filters, and calculated columns in the Data Flow. They can also be passed into containers, processes, and dynamic expressions in the Master Flow.
Note: this feature is available only with an Enterprise edition license.
SQL vs Static Variables
Variables are used to store values which can then be passed to other processes in the Data Flow and Master Flow. Variables can be initialized from a given static value or from a SQL expression that queries the database. In the former case the initialization value is constant, while in the latter it is dynamic and resolved only at runtime.
- Click here to learn about SQL vs static variables.
Add a Variable
Use the Set Variable node to link a variable to the flow. Connect the Set Variable node to the flow, and pass a variable into the node from the Properties panel.
Variable Properties
Display Name
The name of the Set Variable node. By default, the node will be called 'Set Variable 1', 'Set Variable 2', and so on. You can change the node name from this field.
Description
You can add a description to the node; this can be a useful way of documenting the node for yourself and other users.
Set Variable
This is where you'll inject the variable into the node. From the drop down, choose the required variable. From the next field, add a static value or PQL expression.
Create a New Variable
New variables are created from the Variables panel. You can also edit or delete existing variables.
The Variables panel lists all variables that have been added to the data flow. Pyramid automatically adds the 'IsPreview' variable; a simple script can be added to an SQL node to prevent the Preview panel from loading a preview of the data.
- Click here to learn how to create, edit, and delete variables from the Variables Panel.
Example
In this example, the Set Variable node is used to store a value passed from the Execute Process node. The Execute process node is used to ping the server (red highlight below), and the results are loaded into a variable called PingResult (green highlight below):
The Conditional node is used to determine which node to execute next based on the ping result. If the ping request succeeds, an email will be sent; if it fails the Set Variable node will be executed:
StartsWith(@PingResult, "Ping request could not find")
The variable called IpValue is injected into the Set Variable node, and its value is set to a PQL expression that will return the IP address of the URL that was pinged:
SubString(@PingResult,IndexOf(@PingResult, "[") + 1, IndexOf(@PingResult, "]"))
Here we see the PingResult variable (red highlight below) value after executing the flow shows that the ping failed. The Set Variable node was then executed, returning the IP address:
